create sequence t_order_serialnumber
start with 1
increment by 1
nomaxvalue
nocycle
cache 100;

create table t_order_serial_mark (
       serial_mark number,
       last_time date
)

insert into t_order_serial_mark(serial_mark,last_time) values(0,sysdate); 

create or replace procedure pro_order_serialnumber(serialnumber out varchar2)
is
       data_chars varchar2(8);
       current_num varchar2(8);
       zero_chars varchar2(8) := '00000000';
begin
       update t_order_serial_mark
       set 
              serial_mark = seq_order_serialnumber.currval,
              last_time = sysdate
       where  trunc(sysdate, 'dd') > last_time;
       commit;
       
       select to_char(sysdate, 'yyyymmdd') into date_chars from dual;
       select (seq_order_serialnumber.nextval - serial_mark) into current_num from t_order_serial_mark;
       select substr(zero_chars, length(''||current_num) + 1) into zero_chars from dual;
       
       serialnumber := date_chars || zero_chars || current_num;
end;

declare
       serialnumber varchar2(16);
begin
       pro_order_serialnumber(serialnumber);
       
       dbms_output.put_line(serialnumber);
end;
